﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace T4
{
    /// <summary>
    /// 
    /// </summary>
    public class DBInfo
    {
        public static List<TableInfo> GetTableInfo(ModelInfo minfo)
        {
            var DBContext = new DBTools.DapperContext("db", minfo.ConnectionString);

            string sql = @"SELECT `information_schema`.`COLUMNS`.`TABLE_SCHEMA`
                                                      ,`information_schema`.`COLUMNS`.`TABLE_NAME`
                                                      ,`information_schema`.`COLUMNS`.`COLUMN_NAME`
                                                       ,`information_schema`.`COLUMNS`.`DATA_TYPE`
                                                      ,`information_schema`.`COLUMNS`.`COLUMN_COMMENT`
                                                  FROM `information_schema`.`COLUMNS`
                                                   WHERE `information_schema`.`COLUMNS`.`TABLE_NAME` =@TableName and TABLE_SCHEMA=@Schema";

            var list = DBContext.Query<TableInfo>(sql, minfo).ToList();

            if (list != null && list.Count > 0)
            {
                return list;
            }
            else
            {
                throw new Exception("没有找到该表：【" + minfo.TableName + "】");
            }
        }
        public static string GetTableComment(ModelInfo minfo)
        {
            var DBContext = new DBTools.DapperContext("db", minfo.ConnectionString);
            string sql = @"SELECT TABLE_COMMENT FROM `information_schema`.`TABLES`
                                                   WHERE `TABLE_NAME` =@TableName and TABLE_SCHEMA=@Schema";

            var str = DBContext.ExecuteScalar<string>(sql, minfo);
            return str;
        }
        public static string ConvertToCSType(string mysqltype)
        {
            switch (mysqltype)
            {
                case "datetime":
                case "smalldatetime":
                    return "DateTime?";
                case "int":
                case "smallint":
                    return "int";
                case "bit":
                    return "bool";
                case "float":
                    return "double";
                case "decimal":
                case "money":
                case "smallmoney":
                case "numeric":
                    return "decimal";
                case "uniqueidentifier":
                    return "Guid";
                default:
                    return "string";
            }
        }
        public static string GetInsertSql(ModelInfo minfo, List<TableInfo> list)
        {
            string fileds = string.Join(",", list.Select(c => c.COLUMN_NAME).ToArray());
            string values = "@" + string.Join(",@", list.Select(c => c.COLUMN_NAME).ToArray());
            return "insert into " + minfo.TableName + "(" + fileds + ") values (" + values + ")";
        }
        public static string GetUpdateSql(ModelInfo minfo, List<TableInfo> list)
        {
            string fields = string.Join(",", list.Where(c => c.COLUMN_NAME.ToUpper() != "ID").Select(c => c.COLUMN_NAME + "=@" + c.COLUMN_NAME));
            var idfield = list.Select(c => c.COLUMN_NAME).FirstOrDefault(c => c.ToUpper() == "ID");

            return "update " + minfo.TableName + " set " + fields + " where " + idfield + "=@" + idfield;
        }

        public static string GetDeleteSql(ModelInfo minfo, List<TableInfo> list)
        {
            var idfield = list.Select(c=>c.COLUMN_NAME).FirstOrDefault(c => c.ToUpper() == "ID");
            return "delete from " + minfo.TableName + " where " + idfield + "=@" + idfield;
        }
        public static string GetSelectSql(ModelInfo minfo, List<TableInfo> list)
        {
            string fileds = string.Join(",", list.Select(c => c.COLUMN_NAME).ToArray());
            return "select " + fileds + " from " + minfo.TableName + " where 1=1";
        }
    }
    [Serializable]
    public class TableInfo
    {
        public string TABLE_SCHEMA { get; set; }
        public string TABLE_NAME { get; set; }
        public string COLUMN_NAME { get; set; }
        public string DATA_TYPE { get; set; }
        public string COLUMN_COMMENT { get; set; }

    }
    [Serializable]
    public class ModelInfo
    {
        public string ConnectionString { get; set; }
        public string Schema { get; set; }
        public string TableName { get; set; }
        public string ModelName { get; set; }
        public string NameSpace { get; set; }
        public string DALName { get; set; }

    }
}
